package com.xm2.demo.system.order.dao;

import com.xm2.demo.model.Form;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import javax.persistence.Table;
import java.util.Collection;
import java.util.Date;
import java.util.List;

/**
 * @Author
 * @Date 2018/12/12 20:01
 * @Description
 */
@Repository
@Table(name = "Form")
@Qualifier("OrderRepository")
public interface OrderRepository extends JpaRepository<Form,Integer> {
    @Query("SELECT f FROM Form f WHERE f.formDate > :startDate and f.formDate < :stopDate")
    List<Form> findByDatePagination(@Param("startDate") Date startDate,@Param("stopDate") Date stopDate);

    @Query(value = "SELECT * FROM form f WHERE form_date > :startDate and form_date < :stopDate LIMIT :startRow,:size",nativeQuery=true)
    List<Form> findByDatePaginationLimit(@Param("startDate")Date startDate, @Param("stopDate") Date stopDate, @Param("startRow") Integer startRow, @Param("size") Integer size);

    @Modifying
    @Query("update Form f set f.stateId = :stateId , f.salesmanName= :name , f.phone = :phone  where f.orderId = :orderId ")
    Integer updateOrderStateAndName(@Param("stateId") int stateId,@Param("orderId") String orderId,@Param("name") String name,@Param("phone") String phone);

    Form findByOrderId(String orderId);

    List<Form> findByUsername(String username);

    List<Form> findByUsername(String username,Pageable pageable);

    @Query(nativeQuery = true,value = "select sum(r.money) from record as r where r.time like :date% ")
    double sum(@Param("date") String date);

    @Query(nativeQuery = true,value = "select sum(f.money) from form as f where f.order_id like :date% group by f.shop having f.shop != '未绑定'")
    double sum1(@Param("date") String date);
}
